package NG::Sys::DB;
use strict;
use warnings;
use utf8;

our %dbhs;

sub get_dbh {
	my ($srv,$noauto) = @_;
	if($noauto or !$srv->{id}){
		my $dbh = Connect($srv,$noauto);
		$dbh->do("SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;");
		return $dbh;
	}
	my $dbh;
	unless($dbh = $dbhs{$srv->{id}}){
		$dbh = $dbhs{$srv->{id}} = Connect($srv);
		$dbh->do("SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;");
		#$dbhs{$srv}->do("set names  utf8");
		#$dbhs{$srv}->do("set character_set_client='utf8'");
		#$dbhs{$srv}->do("set character_set_results='utf8'");
		#$dbhs{$srv}->do("set collation_connection='utf8_unicode_ci'");
	}
	return $dbh;
}


my %defparams = (
	RaiseError => 0,
	PrintError => 0,
	AutoCommit => 1,
	mysql_enable_utf8 => 1,
	mysql_auto_reconnect => 1,
);

sub Connect {
	my ($srv,$noauto) = @_;
	my $dsn = 'DBI:mysql:database='.$srv->{base};
	if($srv->{local}){
		$dsn .= ';mysql_socket='.$srv->{sock};
	}else{
		$dsn .= ";host=".$srv->{host};
		if($srv->{port}){
			$dsn .= ";port=".$srv->{port};
		}
	}
	my %options = %defparams;
	$options{AutoCommit} = $noauto ? 0 : 1;
	my $dbh = NG::Sys::DB::myDBI->connect($dsn,$srv->{user},$srv->{pass}, \%options);
	unless($dbh){
		require JSON::XS;
		die "DBI::ERROR::connect:(",JSON::XS::encode_json($srv),") ",DBI->errstr,"\n",$dsn,"\n";
	}
	return $dbh;
}

package NG::Sys::DB::myDBI;
use strict;
use warnings;
use utf8;

use DBI;
our @ISA = qw(DBI);

sub connect {
	my $class = shift;
	my $dbh = $class->SUPER::connect(@_);
	return $dbh;
}

package NG::Sys::DB::myDBI::db;
use strict;
use warnings;
use utf8;

use Digest::MD5 qw(md5_hex);

our @ISA = qw(DBI::db);

sub disconnect {
	my $self = shift;
	$self->{private_disconnected} = 1;
	return $self->SUPER::disconnect(@_);
}

sub rows_hash {
	my $self = shift;
	my $query = shift;

	my $sth = $self->prepare_cached($query);
	$sth->execute(@_);
	return $sth->fetchall_arrayref({});
}

sub rows_array {
	my $self = shift;
	my $query = shift;

	my $sth = $self->prepare_cached($query);
	$sth->execute(@_);
	return $sth->fetchall_arrayref;
}

sub Insert {
	my ($self,$table, $fields) = @_;

	my @keys = sort keys %$fields;
	my $query = sprintf('insert into `%s` (%s) values (%s)', $table, join( ',', map {"`".$_."`"} @keys), join(',', ('?') x @keys) );
	my $sth = $self->prepare_cached($query);
	my $rv = $sth->execute(@{$fields}{@keys});
	my $lastid = $sth->{'mysql_insertid'};
	$sth->finish();

	return $lastid;
}

sub Replace {
	my ($self,$table, $fields) = @_;

	my @keys = sort keys %$fields;
	my $query = sprintf('replace into `%s` (%s) values (%s)', $table, join( ',', map {"`".$_."`"} @keys), join(',', ('?') x @keys) );

	my $sth = $self->prepare_cached($query);
	my $rv = $sth->execute(@{$fields}{@keys});
	my $lastid = $sth->{'mysql_insertid'};
	$sth->finish();

	return $lastid;
}
sub Update {
	my ($self, $table, $keys, $fields) = @_;

	my (@cond_val, @cond_name, @set_name, @set_val, %is_key);
	foreach (sort @$keys) {
		push @cond_name,$_;
		if(ref $fields->{$_}){
			push @cond_val, $fields->{$_}[0];
		}else{
			push @cond_val, $fields->{$_};
			$is_key{$_} = 1;
		}
	}
	for my $k(sort keys %$fields){
		my $v = $fields->{$k};
		unless(exists $is_key{$k}){
			push @set_name, $k;
			push @set_val,ref $v ? $v->[1] : $v;
		}
	}
	
	my $query = sprintf('update `%s` set %s where %s', $table,join(", ", map {"`".$_."`=?"} @set_name),join(" and ", map {"`".$_."`=?"} @cond_name));
	my $sth = $self->prepare_cached($query);
	$sth->execute(@set_val,@cond_val);
}
sub Delete {
	my ($self, $table, $keys) = @_;
	my @param;
	my @cond;
	for my $k(sort keys %$keys){
		my $v = $keys->{$k};
		if(ref $v){
			push @cond,"`$k` in (".join(",",("?") x @$v).")";
			push @param, @$v;
		}else{
			push @param, $v;
			push @cond, "`$k` = ?";
		}
	}
	my $cond = @cond ? "where" : "";
	my $query = "delete from `$table` $cond".join(" and ",@cond);
	my $sth = $self->prepare_cached($query);
	my $ret = $sth->execute(@param);
	$sth->finish();
	return $ret;
}
sub column {
	my $self = shift;
	my $query = shift;

	my $sth = $self->prepare_cached($query);
	$sth->execute(@_);
	my $rv = $sth->fetchall_arrayref([0]);
	return wantarray? @$rv : shift @$rv;
}

sub row_array {
	my $self = shift;
	my $query = shift;

	my $sth = $self->prepare_cached($query);
	$sth->execute(@_);
	my @data = $sth->fetchrow;
	$sth->finish();

	return @data;
}
sub row_hash {
	my $self = shift;
	my $query = shift;

	my $sth = $self->prepare_cached($query);
	$sth->execute(@_);
	my $row = $sth->fetchrow_hashref;
	$sth->finish();

	return $row;
}

sub exec {
	my $self = shift;
	my $query = shift;

	my $lastid = 0;
	my $sth = $self->prepare_cached($query);
	my $rc = $sth->execute(@_);
	$lastid = $sth->{'mysql_insertid'};
	$sth->finish();

	return $lastid ? $lastid : $rc;
}
sub prepare_cached {
	my $self = shift;
	my $query =  shift;
	return $self->_getHandle(\$query);
}

sub _getHandle {
	my ($self,$queryref) = @_;
	my $name = md5_hex($$queryref);
	$self->{'private_sths'}->{$name} ||= $self->prepare($$queryref);
	return $self->{'private_sths'}->{$name};
}

sub prepare {
	my $self = shift;
	my $sql = shift;
	my @params;
	while($sql =~ s/\{(\S+?)\}/\?/){
		push @params, $1;
	}
	my $sth = $self->SUPER::prepare($sql,@_) or $self->die_error($sql);
	$sth->{private_query} = $sql;
	$sth->{private_params} = \@params if @params; # Save params order
	return $sth;
}

sub DESTROY {
	my $self = shift;
	# dismiss warning while clening on final
	local $SIG{__WARN__} = sub{};
	$self->disconnect unless $self->{private_disconnected};
	$self->SUPER::DESTROY(@_);
}
sub die_error {
	my $self = shift;
	my $sql = shift;
	die "DBI::PREPARE::ERROR: ",$DBI::errstr ,"\n",
		"Query: ",$sql,
		"\n";
}


package NG::Sys::DB::myDBI::st;
use strict;
use warnings;
use utf8;

our @ISA = qw(DBI::st);
use Data::Dumper;
sub execute {
	my $self = shift;
	my $rv;
	my $p;
	if ( defined $self->{private_params} ) {
		if(@_ % 2){
			die "odd number params\n";
		}
		my %args = @_;
		my @p = @args{@{$self->{private_params}}};
		$p = \@p;
	}else{
		$p = \@_;
	}
	$rv = $self->SUPER::execute(@$p) or $self->die_error($p);
	return $rv;
}

sub die_error {
	my $self = shift;
	my $param = shift;
	my $str =  "DBI::EXECUTE::ERROR($DBI::err): ".$DBI::errstr ."\n".
		"Query: ".$self->{private_query}."\n".
		"Param: ".join(", ",
					map {"'".$_."'"}
					map {s/(.)/ ord($1) < 32 ? sprintf("\\0x%02X",ord($1)) : $1 /ge ; $_}
					map {length $_ > 20? substr($_,0,20):$_}
					map {defined $_?$_:"<undef>"} @$param).
		"\n";
	die $str;
}


1;
